﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Common;
using CNCSoft.ErpApps.TMS.myclass;
using DevExpress.XtraGrid.Views.Grid;
using CNCSoft.ErpApps.TMS.longGiaoVu;
using CNCSoft.EData;

namespace CNCSoft.ErpApps.TMS
{
    public partial class frmDangKyLop : Form
    {
        private string userid = GlobalVariable.WSInfo.UserID;
        private string companyid = GlobalVariable.WSInfo.CompanyID;
        private SqlConnection conn = null;
        private DataSet ds = null;
        private CommonClass mine;
        private Calculate cal;
        private string[] selectedValue;
        private DataTable dt;
        private bool sign = false;
        private bool signStudentInClass = false;
        private bool signStudentInAvailableClass = false;
        private int sogiohoc;
        bool cancelHandlers;
        //BindingManagerBase bmb;

        public frmDangKyLop()
        {
            InitializeComponent();
        }

        private void frmDangKyLop_Load(object sender, EventArgs e)
        {
            //----------------------------
            //gridControlDataInClass.Enabled = false;
            //btnAddMoreStudent.Visible = false;
            //btnPick.Visible = false;
            //----------------------------
            string userID = GlobalVariable.WSInfo.UserID;
            //cancelHandlers = true;
            //if (userID.ToLower() == "test" || userID.ToLower() == "tuptc")
                //btnPick.Enabled = true;
            //else
            //    btnPick.Enabled = false;

            conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
            ds = new DataSet();
            mine = new CommonClass();
            cal = new Calculate();
            try
            {
                LoadControl();
                dt = new DataTable("TableStudent");
                btnAddMoreStudent.Enabled = true;
            }
            catch 
            { 
            }
            //finally { cancelHandlers = false; }
        }

        //private void btnRegistrate_Click(object sender, EventArgs e)
        //{
        //    try
        //    {
        //        if (textEditClassName.Text != "")
        //            DangKy();
        //        else
        //            MessageBox.Show("Bạn chưa nhập tên lớp hoặc doanh thu hoặc sỉ số của lớp");
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //}

        //private void radioGroup_InClass_SelectedIndexChanged(object sender, EventArgs e)
        //{
        //    try
        //    {
        //        string makhoahoc = searchLookUpEditListClass.EditValue.ToString();
        //        string value = radioGroup_InClass.EditValue.ToString();
        //        gridViewDataInAvailableClass.Columns.Clear();
        //        InClassChoice(value, makhoahoc);
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //}

        //private void simpleButtonCalEndDate_Click(object sender, EventArgs e)
        //{
        //    TinhNgayKetThuc();
        //}

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Refresh();
        }

        
       
        //select class
        private void searchLookUpEditListClass_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                
                string makhoahoc = searchLookUpEditListClass.EditValue.ToString();
                LoadGridStudentInAvailableClass(makhoahoc);     
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //ham luu data
        private void RegistStudentToAvailableClass()
        {
            try
            {
                if (searchLookUpEditListClass.Text != "")
                {
                    
                    int k = 0;
                    string strField = null;
                    string makhoahoc = searchLookUpEditListClass.EditValue.ToString();
                    
                    selectedValue = mine.ChooseAllValue(gridViewDataInAvailableClass, strField);

                    //Xoa het HS trong lop nay de insert lai
                    //SQLHelper.ExecuteNonQuery("DELETE FROM Invoice_KhoaHoc WHERE MaKhoaHoc = '" + makhoahoc + "'", GlobalVariable.WSInfo.ConnectionString);

                    for (int i = 0; i < selectedValue.Length;)
                    {
                        string sql = @"INSERT INTO Invoice_KhoaHoc(InvoiceNumber, MaKhoaHoc) VALUES(@invoicenumber, @makhoahoc)";
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        cmd.Parameters.AddWithValue("@invoicenumber", selectedValue[i]);//sai lam cho nay
                        cmd.Parameters.AddWithValue("@makhoahoc", makhoahoc);
                        
                        conn.Open();
                        k = cmd.ExecuteNonQuery();
                        conn.Close();
                        i++;                        
                    }

                    if (k > 0)
                    {
                        MessageBox.Show("Thêm học sinh vào lớp đã có thành công");
                        //ds.Tables["TableStudentInClass"].Rows.Clear();
                    }
                    else
                        MessageBox.Show("Thất bại");
                }
                else
                {
                    MessageBox.Show("Bạn chưa chọn lớp để thêm học sinh");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }

        //luu data
        private void btnAddMoreStudent_Click(object sender, EventArgs e)
        {
                RegistStudentToAvailableClass();
        }

        private void btnEditStartDate_Click(object sender, EventArgs e)
        {
            
        }

        
        //private void btn_DanhSachLop_Click(object sender, EventArgs e)
        //{
        //    try
        //    {
        //        frmDanhSachKH = new frmDanhSachKhoaHoc();
        //        frmDanhSachKH.ShowDialog();
        //    }
        //    catch (Exception ex)
        //    {
                   
        //    }
        //}

        
        private void gridViewDataInAvailableClass_RowCellStyle(object sender, RowCellStyleEventArgs e)
        {
            try
            {
                if (e.RowHandle >= 0)
                {
                    DateTime ngaydongtien = Convert.ToDateTime(gridViewDataInAvailableClass.GetRowCellValue(e.RowHandle, gridViewDataInAvailableClass.Columns["NgayDongTien"]));
                    DateTime ngaykhaigiang = Convert.ToDateTime(gridViewDataInAvailableClass.GetRowCellValue(e.RowHandle, gridViewDataInAvailableClass.Columns["NgayKhaiGiang"]));
                    if (ngaydongtien > ngaykhaigiang)
                    {
                        e.Appearance.BackColor = Color.Salmon;
                    }
                }               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region coding
        private void LoadControl()
        {
            try
            {
                //LoadTrinhDo();
                //LoadNgayTrongtuan();
                //LoadGioHoc();
                LoadReadyClass();
               // dateEditBeginDate.EditValue = DateTime.Now.ToShortDateString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //private void LoadTrinhDo()
        //{
        //    try
        //    {
        //        if (ds.Tables["TableBangCap"] != null)
        //            ds.Tables["TableBangCap"].Clear();
        //        string sql = @"SELECT * FROM DM_BANGCAP WHERE MaDonVi = '" + companyid + "'";
        //        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        //        da.Fill(ds, "TableBangCap");
        //        comboBoxTrinhDo.DataSource = ds.Tables["TableBangCap"];
        //        comboBoxTrinhDo.ValueMember = "MaBangcap";
        //        comboBoxTrinhDo.DisplayMember = "TenBangcap";
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //}

        //private void LoadNgayTrongtuan()
        //{
        //    string sql = @"SELECT * FROM DM_NGAYTRONGTUAN WHERE MaNgayTrongTuan IN ('01', '02', '20', '21')";
        //    mine.ExecuteDataAdapter(sql, conn, ds, "TableNgayTrongTuan", "MaNgayTrongTuan", "TenNgayTrongTuan", comboBoxNgayTrongTuan);
        //}

        //private void LoadGioHoc()
        //{
        //    string sql = @"SELECT * FROM DM_GIOHOC";
            
        //    mine.ExecuteDataAdapter(sql, conn, ds, "TableGioHoc", "MaGioHoc", "TenGioHoc", comboBoxGioHoc);
        //}

        private void LoadGridStudentInAvailableClass(string makhoahoc)
        {
            try
            {
                if (signStudentInAvailableClass)
                    ds.Tables["TableStudentInAvailableClass"].Rows.Clear();
                /*
                string sql =
                    @"SELECT inv.InvoiceNumber,enq.Holot,enq.Ten,enq.DienthoaiDidong,dmkh.TenKhoaHoc,inv.InvoiceDate,dmkh.NgayBatDau,enq.Counsellor,Convert(varchar, cast(invDetail.SoTien as money), 1) AS SoTien
                    FROM DM_KHOAHOC dmkh LEFT OUTER JOIN Invoice inv ON inv.MaLop = dmkh.MaKhoaHoc
					INNER JOIN InvoiceDetail invDetail ON invDetail.InvoiceNumber = inv.InvoiceNumber
                    INNER JOIN Enquiry enq ON enq.EnquiryNumber = inv.EnquiryNumber
                    INNER JOIN SYS_List_User usd ON usd.UserID = inv.Counsellor
                    WHERE dmkh.MaDonVi = '" + GlobalVariable.WSInfo.CompanyID + @"'
                    AND dmkh.MaKhoaHoc = '" + makhoahoc + @"' AND inv.InvoiceNumber NOT IN (SELECT InvoiceNumber FROM [dbo].[Invoice_KhoaHoc])";
                */  //Lay tat ca hoc sinh trong lop dua len roi bo sung them chu khong phai chi lay HS chua co trong lop (chi vi muc dich bo sung)
                string sql =
                  @"SELECT inv.InvoiceNumber,enq.Holot,enq.Ten,enq.DienthoaiDidong,dmkh.TenKhoaHoc,inv.InvoiceDate,dmkh.NgayBatDau,enq.Counsellor, Malop,	Convert(varchar, cast(invDetail.SoTien as money), 1) AS SoTien
                  FROM Invoice inv
                  JOIN DM_KHOAHOC dmkh ON inv.MaLop = dmkh.MaKhoaHoc
                  JOIN InvoiceDetail invDetail ON invDetail.InvoiceNumber = inv.InvoiceNumber
                  JOIN Enquiry enq ON enq.EnquiryNumber = inv.EnquiryNumber
                  JOIN SYS_List_User usd ON usd.UserID = inv.Counsellor
                  WHERE inv.MaDonVi = '" + GlobalVariable.WSInfo.CompanyID + @"'
                  AND MaLop = '" + makhoahoc + @"'";


                if (gridViewDataInAvailableClass.RowCount > 0)
                {
                    gridViewDataInAvailableClass.Columns.Clear();
                }

                DataSet dsResult = new DataSet();
                dsResult = mine.ExecDataReturnDataSet(@"SELECT [TenNgayTrongTuan] AS NgayHoc,[TenGiohoc] AS GioHoc,[SoGioHoc] AS TongGioHoc,[SoNgayHoc] AS TongNgayHoc,[NgayBatDau],[NgayKetThuc],[DoanhThu],[NgayMoLop]    
                                                        FROM [dbo].[DM_KHOAHOC] AS A LEFT JOIN [dbo].[DM_NGAYTRONGTUAN] AS B ON A.MaNgayTrongTuan = B.MaNgayTrongTuan
                                                        LEFT JOIN [dbo].[DM_GIOHOC] AS C ON A.MaTimeSlot = C.MaGiohoc
                                                        WHERE MaKhoaHoc='" + makhoahoc + "'", conn);
                foreach(DataRow dr in dsResult.Tables[0].Rows)
                {
                    lblNgayHoc.Text = dr["NgayHoc"].ToString();
                    lblTongNgayHoc.Text = dr["TongNgayHoc"].ToString();
                    lblGioHoc.Text = dr["GioHoc"].ToString();
                    lblTongGioHoc.Text = dr["TongGioHoc"].ToString();
                    lblNgayBatDau.Text = String.Format("{0:dd/MM/yyyy}", dr["NgayBatDau"]);
                    lblNgayKetThuc.Text = String.Format("{0:dd/MM/yyyy}", dr["NgayKetThuc"]);
                    lblDoanhThu.Text = String.Format("{0:0,000}", dr["DoanhThu"]);
                    lblNgayMoLop.Text = String.Format("{0:dd/MM/yyyy}", dr["NgayMoLop"]);
                }

                mine.ExecDataAdapter(sql, conn, ds, "TableStudentInAvailableClass", gridControlDataInAvailableClass);  //Chi don thuan la gan datasource cua datagrid cho dataset.datatable
                signStudentInAvailableClass = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

        //private void LoadGridStudentInAvailableClassBaoLuu(string makhoahoc)
        //{
        //    try
        //    {
        //        if (ds.Tables["TableHocSinhBaoLuu"] != null)
        //            ds.Tables["TableHocSinhBaoLuu"].Clear();
        //        string sql = @"usp_NhungHocSinhBaoLuuCuaMotLop @makhoahoc = '" + makhoahoc + "'";
        //        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        //        da.Fill(ds, "TableHocSinhBaoLuu");
        //        if (gridViewDataInAvailableClass.RowCount > 0)
        //        {
        //            gridViewDataInAvailableClass.Columns.Clear();
        //        }
        //        gridControlDataInAvailableClass.DataSource = ds.Tables["TableHocSinhBaoLuu"];
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //}

        private void RemoveStudent(GridView grv)
        {
            grv.DeleteSelectedRows();
        }

        private void LoadReadyClass()
        {
            try
            {
                string sql = @"SELECT MaKhoaHoc, TenKhoaHoc, MaBangCap FROM DM_KHOAHOC WHERE MaDonVi = '" + GlobalVariable.WSInfo.CompanyID + "' AND TrangThai = 'Ready'";
                mine.ExecuteDataAdapter(sql, conn, ds, "TableKhoaHoc", "MaKhoaHoc", "TenKhoaHoc", searchLookUpEditListClass);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

//        private void DangKy()
//        {
//            try
//            {
//                Validation();
//                DataTable dt2 = new DataTable();
//                string strField = null;
//                selectedValue = mine.ChooseAllValue(gridViewDataInClass, strField);
//                if(selectedValue.Length > 0)
//                    dt2 = mine.GridToTable(gridViewDataInClass);
//                //------------------------------------------------------------------
//                int i = 0;
//                string malop = "";
//                string tenlop = textEditClassName.EditValue.ToString();
//                DateTime date;

//                double doanhthulop = 0;
//                int siso = 0;

//                if(txtDoanhThuLop.Text != "")
//                    doanhthulop = Convert.ToDouble(txtDoanhThuLop.Text);
//                if(txtSiSoLop.Text != "")
//                    siso = Convert.ToInt32(txtSiSoLop.Text);

//                if (dateEditEndDate.Text != "")
//                {
//                    DateTime ngayketthuc = Convert.ToDateTime(String.Format("{0:MM/dd/yyyy}", dateEditEndDate.EditValue));

//                    if (tenlop.Equals(""))
//                    {
//                        MessageBox.Show("Bạn chưa nhập tên lớp");
//                        return;
//                    }
//                    malop = tenlop;
//                    date = Convert.ToDateTime(String.Format("{0:MM/dd/yyyy}", dateEditBeginDate.DateTime));
//                    string ngayhoctrongtuan = comboBoxNgayTrongTuan.SelectedValue.ToString();
//                    string giohoc = comboBoxGioHoc.SelectedValue.ToString();
//                    string mabangcap = comboBoxTrinhDo.SelectedValue.ToString().Trim();

//                    // insert data into DM_KHOAHOC
//                    string sql =
//                        @"INSERT INTO DM_KHOAHOC
//                        (MaKhoaHoc, TenKhoaHoc, MaNgayTrongTuan, MaTimeSlot, SoGioHoc, SoNgayHoc, 
//                        NgayBatDau, NgayKetThuc, MaDonVi, TrangThai, DoanhThu, ChiSo, MaBangCap)
//                        VALUES
//                        (@makhoahoc, @tenkhoahoc, @ngaytrongtuan, @giohoc, @sogiohoc, 
//                        @songayhoc, @ngaybatdau, @ngayketthuc, @madonvi, @trangthai, @doanhthu, @chiso, @mabangcap)";
//                    SqlCommand cmd = new SqlCommand(sql, conn);
//                    SqlParameter param = new SqlParameter();

//                    cmd.Parameters.AddWithValue("@makhoahoc", malop);
//                    cmd.Parameters.AddWithValue("@tenkhoahoc", tenlop);
//                    cmd.Parameters.AddWithValue("@ngaytrongtuan", ngayhoctrongtuan);
//                    cmd.Parameters.AddWithValue("@sogiohoc", Convert.ToInt32(textEditSoGioHoc.Text));
//                    cmd.Parameters.AddWithValue("@giohoc", giohoc);
//                    cmd.Parameters.AddWithValue("@songayhoc", Convert.ToInt32(textEditTongSoNgay.Text));
//                    cmd.Parameters.AddWithValue("@ngaybatdau", date);
//                    cmd.Parameters.AddWithValue("@ngayketthuc", ngayketthuc);
//                    cmd.Parameters.AddWithValue("@madonvi", GlobalVariable.WSInfo.CompanyID);
//                    cmd.Parameters.AddWithValue("@trangthai", "Waiting");
//                    cmd.Parameters.AddWithValue("@doanhthu", doanhthulop);
//                    cmd.Parameters.AddWithValue("@chiso", siso);
//                    cmd.Parameters.AddWithValue("@mabangcap", mabangcap);
//                    try
//                    {
//                        conn.Open();
//                        i = cmd.ExecuteNonQuery();
//                    }
//                    finally
//                    {
//                        if (conn != null)
//                            conn.Close();
//                    }

//                    if (i > 0)
//                    {
//                        MessageBox.Show("Tạo lớp thành công");
//                        LoadReadyClass();
//                    }
//                }
//                else
//                {
//                    //MessageBox.Show("Bạn chưa có ngày kết thúc, xin vui lòng bạn tính ngày kết thúc");
//                    //DangKy();
//                }
//            }
//            catch (Exception ex)
//            {
//                //MessageBox.Show("Bạn chưa nhập tên lớp hay lớp này đã tạo rùi, vui lòng đặt tên khác");
//                DangKy();
//            }
//        }

        

        private void InClassChoice(string value, string makhoahoc)
        {
            try
            {
                if (value == "all")
                {
                    LoadGridStudentInAvailableClass(makhoahoc);
                }
                //else if (value == "baoluu")
                //{
                //    LoadGridStudentInAvailableClassBaoLuu(makhoahoc);
                //}
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
//        //tinh ngay ket thuc
//        private void TinhNgayKetThuc()
//        {
//            try
//            {
                
//                DateTime startdate = Convert.ToDateTime(String.Format("{0:MM/dd/yyyy}", dateEditBeginDate.EditValue));
//                int sogiohoc = Convert.ToInt32(textEditSoGioHoc.Text);
//                string strsql = "SELECT A.MaNgayTrongTuan FROM [dbo].[DM_NGAYTRONGTUAN] AS A LEFT JOIN [dbo].[DM_KHOAHOC] AS B ON A.MaNgayTrongTuan = B.MaNgayTrongTuan WHERE B.MaKhoaHoc ='" + searchLookUpEditListClass.Text+"'";
//                string mangaytrongtuan = this.GetColumnForGridView(strsql).ToString();
                

//                string sql = @"usp_CalEndDateWithRestDate @ngaybatdau = '" + startdate + @"', 
//                                @sogiohoc = " + sogiohoc + @", @mangaytrongtuan = '" + mangaytrongtuan + @"'";
                
                
//                lblNgayKetThuc.Text = SQLHelper.ExecuteScalar(conn, sql).ToString();
//            }
//            catch (Exception ex)
//            {
//                MessageBox.Show("Bạn chưa nhập đầy đủ để tính thời gian kết thúc, xin mời bạn bổ sung !!!");
//            }
//        }

        private void CallFormEditClass(string makhoahoc)
        {
            try
            {
                if (searchLookUpEditListClass.Text != "")
                {
                    frmEditKhoaHoc form_edit_khoahoc = new frmEditKhoaHoc(makhoahoc);
                    form_edit_khoahoc.ShowDialog();
                    
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void PickStudentOutOfClass(string id)
        {
            try
            {
                int i = 0;
                string sql =
                    @"DELETE FROM Invoice_KhoaHoc WHERE ID = @id";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", id);
                try
                {
                    conn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                if (i > 0)
                {
                    MessageBox.Show("Lấy học sinh ra khỏi lớp thành công");
                    LoadGridStudentInAvailableClass(searchLookUpEditListClass.EditValue.ToString());
                }
                else
                    MessageBox.Show("Lấy học sinh ra khỏi lớp thất bại");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion
        
        //chọn add data cho gridview
        string invoiceNumber = "";
        private void repItxtTen_Enter(object sender, EventArgs e)
        {
            frmView fv = new frmView("SELECT B.InvoiceNumber,Holot,Ten,DienthoaiDidong,MaLop,InvoiceDate,StartDate,B.Counsellor,CONVERT(varchar(20),C.SoTien, 1) AS SoTien FROM Enquiry AS A LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber LEFT JOIN InvoiceDetail AS C ON B.InvoiceNumber = C.InvoiceNumber WHERE B.InvoiceNumber NOT IN (SELECT InvoiceNumber FROM Invoice_KhoaHoc)", "EnquiryDate");
            fv.ShowDialog(this);

            if (fv.FlChoose)
            {
              
               invoiceNumber = fv.SelectedIDs.Replace("('", "").Replace("')", "").ToString();

               gridViewDataInAvailableClass.SetFocusedRowCellValue("InvoiceNumber", invoiceNumber);

               string cotTen = this.GetColumnForGridView(@"SELECT Ten FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("Ten", cotTen);

               string hoLot = this.GetColumnForGridView(@"SELECT Holot FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("Holot", hoLot);

               string dienthoai = this.GetColumnForGridView(@"SELECT DienthoaiDidong FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("DienthoaiDidong", dienthoai);

               string malop = this.GetColumnForGridView(@"SELECT MaLop FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("TenKhoaHoc", malop);

               string invoicedate = this.GetColumnForGridView(@"SELECT InvoiceDate FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("InvoiceDate", invoicedate);

               string startdate = this.GetColumnForGridView(@"SELECT StartDate FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("NgayBatDau", startdate);

               string counsellor = this.GetColumnForGridView(@"SELECT B.Counsellor FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               gridViewDataInAvailableClass.SetFocusedRowCellValue("Counsellor", counsellor);

               string money = this.GetColumnForGridView(@"SELECT C.SoTien FROM Enquiry AS A
                                                     LEFT JOIN Invoice AS B ON A.EnquiryNumber = B.EnquiryNumber
                                                     LEFT JOIN InvoiceDetail AS C ON B.InvoiceNumber = C.InvoiceNumber
                                                     WHERE B.InvoiceNumber='" + fv.SelectedIDs.Replace("('", "").Replace("')", "") + "'").ToString();
               
               gridViewDataInAvailableClass.SetFocusedRowCellValue("SoTien", money);
            }			


        }
        
        
        //get column for gridview
        private object GetColumnForGridView(string sql) {
            
            object col =  null;
            try
            {
                col = SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, sql);
                return col;
            }
            catch { return col; }
            
        }

        
        private void btnXoa_Click(object sender, EventArgs e)
        {
            try
            {
                string[] choosedValues = new string[2];
                choosedValues = mine.ChoosedValue(gridViewDataInAvailableClass, null);
                if (!string.IsNullOrEmpty(choosedValues[0].ToString()))
                {
                                      
                    string id = choosedValues[0].ToString();
                    PickStudentOutOfClass(id);
                }
                else
                {
                    MessageBox.Show("Mời chọn học sinh muốn chọn");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnEditLop_Click(object sender, EventArgs e)
        {
            try
            {
                if (searchLookUpEditListClass.Text != "")
                {
                    string makhoahoc = searchLookUpEditListClass.EditValue.ToString();
                    CallFormEditClass(makhoahoc);
                }
                else
                {
                    MessageBox.Show("Xin mời bạn chọn lớp để chỉnh sửa");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gridControlDataInAvailableClass_Click(object sender, EventArgs e)
        {

        }

       
    }//the end class
}//the end namespace